if exists(select 1
		from information_schema.routines
		where routine_type = 'PROCEDURE'
		and routine_name = 'p_drop_proc_if_exists')
		exec ('drop procedure p_drop_proc_if_exists');

go

--Function of dropping a procedure
create procedure p_drop_proc_if_exists(
	--Target procedure name
	@a_proc_name varchar(255))
as
begin	
	if exists(select 1
		from information_schema.routines
		where routine_type = 'PROCEDURE'
		and routine_name = @a_proc_name)
		exec ('drop procedure ' + @a_proc_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_func_if_exists';

go

--Function of dropping a function
create procedure p_drop_func_if_exists(
	--Target function name
	@a_func_name varchar(255))
as
begin
	if exists(select 1
		from information_schema.routines
		where routine_type = 'FUNCTION'
		and routine_name = @a_func_name)
		exec ('drop function ' + @a_func_name);
end;

go

exec dbo.p_drop_func_if_exists 'p_bin_to_str';

go

--Function of converting binary(16) to string
create function p_bin_to_str(
     @binvalue binary(16))
returns varchar(32)
as
begin
	declare @charvalue varchar(32);
	declare @i int;
	declare @length int;
	declare @hexstring char(16);

	set @charvalue = '';
	set @i = 1;
	set @hexstring = '0123456789abcdef';
	set @length = datalength(@binvalue);

	while (@i <= @length)
	begin
		declare @tempint int
		declare @firstint int
		declare @secondint int

		set @tempint = convert(int, substring(@binvalue,@i,1))
		set @firstint = floor(@tempint/16)
		set @secondint = @tempint - (@firstint*16)

		set @charvalue = @charvalue +
		substring(@hexstring, @firstint+1, 1) +
		substring(@hexstring, @secondint+1, 1)

		set @i = @i + 1
	end;
	
	return @charvalue;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_dbms_name';

go

--Function that gets dbms name
create procedure meta_get_dbms_name(
	@a_dbms_name varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_dbms_name = 'SQLSERVER';
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_dbms_version';

go

--Function that gets dbms version
create procedure meta_get_dbms_version(
	@a_dbms_version varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_dbms_version = convert(varchar(64), serverproperty('productversion'));
end;

go

exec dbo.p_drop_proc_if_exists 'meta_get_current_db_name';

go

--Function that return database name
create procedure meta_get_current_db_name(
	@a_db_name varchar(64) output)
with execute as owner
as
begin
    set nocount on;
    select @a_db_name = db_name();
end;

go

exec dbo.p_drop_func_if_exists 'p_get_table_name';

go

--Function of getting table name
create function p_get_table_name(
    --Table id
    @a_table_id binary(16))
    returns varchar(26)
as
begin
	--Table name
	declare @target_table_name varchar(30);
	
    if @a_table_id is null
    begin
        return null;
    end;

    select @target_table_name = table_name
    from meta_tables where table_id = @a_table_id;
    
    return @target_table_name;
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_table_if_exists';

go

--Function of dropping a table
create procedure p_drop_table_if_exists(
    --Target table name
    @a_table_name varchar(30))
as  
begin
	declare @table_count integer;
	
    select @table_count = count(*) from information_schema.tables
    where table_name = lower(@a_table_name)
    and table_type = 'BASE TABLE';
    
    if @table_count > 0
        execute ('drop table ' + @a_table_name);
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_view_if_exists';

go

--Function of dropping a view
create procedure p_drop_view_if_exists(
    --Target view name
    @a_view_name varchar(30))
as
begin
	declare @view_count integer;
	
    select @view_count = count(*) from information_schema.tables
    where table_name = lower(@a_view_name)
    and table_type = 'VIEW';
    
    if @view_count > 0
        execute ('drop view ' + @a_view_name);
end;

go

exec dbo.p_drop_proc_if_exists 'meta_soft_delete_table_row';

go

--Function of soft deleting a table row
create procedure meta_soft_delete_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be deleted
    if object_id('tempdb..##rows_to_be_soft_deleted') is null --Creating a #temp table is slow
		create table ##rows_to_be_soft_deleted(
			--Table id
			table_id binary(16) not null,
			--Table name
			table_name varchar(26) not null,
			--Row id
			row_id binary(16) primary key);
		
	--A table that stores deleted rows
	declare @deleted_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to delete the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_soft_deleted values(@a_table_id, @a_curr_table_name, @a_row_id);

    while (exists(select 1 from ##rows_to_be_soft_deleted))
    begin
		--Getting a row to be deleted
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_table_name = table_name,
			@a_curr_row_id = row_id
		from ##rows_to_be_soft_deleted;
		
		delete ##rows_to_be_soft_deleted
		where row_id = @a_curr_row_id;
		
		--If the current row is not deleted already	
		if not exists (select 1 from @deleted_rows where row_id = @a_curr_row_id)
		begin
		
			declare
			--Ref table id
			@a_ref_table_id binary(16),
			--Ref table name
			@a_ref_table_name varchar(26),
			--Ref column name
			@a_ref_column_name varchar(26),
			--Row count
			@a_row_count int;
			 
			--Enumerating all references on the current table
			declare @ref_cursor cursor;
			set @ref_cursor = cursor fast_forward read_only for
			select column_id, cascade_delete from meta_ref_targets
			where ref_table_id = @a_curr_table_id;
			 
			declare @a_ref_column_id binary(16),
			@a_ref_cascade_delete numeric(1);

			open @ref_cursor;
			fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;

			while @@fetch_status = 0
			begin     
				--Reading ref column properties
				select @a_ref_column_name = column_name,
				@a_ref_table_id = table_id
				from dbo.meta_ref_columns
				where column_id = @a_ref_column_id;

				set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);

				--If cascade delete is forbidden
				if @a_ref_cascade_delete = 0
				begin
					--Checking reference value existence
					declare @check_ref_existence_sql nvarchar(500);
					set @check_ref_existence_sql = 'select @row_count_out = 1 where exists(select 1 from tbl_' + @a_ref_table_name
					+ ' where ' + @a_ref_column_name + ' = @row_id and is_active = 1)';

					exec sp_executesql @check_ref_existence_sql,
					N'@row_id binary(16), @row_count_out int output', 
					@row_id = @a_curr_row_id,
					@row_count_out = @a_row_count output;	

					--If there are references on the current row
					if @a_row_count > 0
					begin
						declare @a_row_id_str varchar(32);
						set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);
					
						raiserror('Deleting when there are references on the row (id = %s) with CascadeDelete = false (#error_code = 20001)',
						16, 1, @a_row_id_str);
						rollback;
						return;
					end;
				end;
	    
				--Deleting other reference values too (putting them in the list of row to be deleted)
				declare @delete_other_rows_sql nvarchar(500);
				set @delete_other_rows_sql = 'insert into ##rows_to_be_soft_deleted(row_id, table_id, table_name)'
				+ ' select id, @table_id, @table_name from tbl_' + @a_ref_table_name
				+ ' where ' + @a_ref_column_name + ' = @row_id'
				+ ' and id not in (select row_id from ##rows_to_be_soft_deleted)';

				exec sp_executesql @delete_other_rows_sql,
				N'@row_id binary(16), @table_id binary(16), @table_name varchar(26)', 
				@row_id = @a_curr_row_id,
				@table_id = @a_ref_table_id,
				@table_name = @a_ref_table_name;

				fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;
			end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Deleting the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'update tbl_' + @a_curr_table_name
			+ ' set is_active = 0, deleted_by = lower(original_login()), deleted_on = getdate() where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the deleted rows list
			insert @deleted_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_restore_table_row';

go

--Function of restoring a table row
create procedure meta_restore_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be restored
    if object_id('tempdb..##rows_to_be_restored') is null --Creating a #temp table is slow
		create table ##rows_to_be_restored(
			--Table id
			table_id binary(16) not null,
			--Table name
			table_name varchar(26) not null,
			--Row id
			row_id binary(16) primary key);
		
	--A table that stores restored rows
	declare @restored_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to restore the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_restored values(@a_table_id, @a_curr_table_name, @a_row_id);

    while (exists(select 1 from ##rows_to_be_restored))
    begin
		--Getting a row to be restored
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_table_name = table_name,
			@a_curr_row_id = row_id
		from ##rows_to_be_restored;
		
		delete ##rows_to_be_restored
		where row_id = @a_curr_row_id;
		
		--If the current row is not restored already	
		if (not exists(select 1 from @restored_rows where row_id = @a_curr_row_id))
		begin
			declare
			--Ref table name
			@a_ref_table_name varchar(26),
			--Row count
			@a_row_count int,
			--Reference value
			@a_ref_value binary(16),
			--Reference value was found
			@a_ref_value_was_found numeric(1);
		
			--Enumerating all references in this table
			declare @ref_column_id binary(16),
			@ref_column_name varchar(26),
			@ref_cursor cursor;

			set @ref_cursor = cursor fast_forward read_only for
			select column_id, column_name from meta_ref_columns
			where table_id = @a_curr_table_id;

			open @ref_cursor;
			fetch next from @ref_cursor into @ref_column_id, @ref_column_name;

			while @@fetch_status = 0
			begin
				--Reading reference value
				declare @read_ref_sql nvarchar(500);
				set @read_ref_sql = 'select @ref_value_out = ' + @ref_column_name + ' from tbl_'
				+ @a_curr_table_name + ' where id = @a_id';
				
				exec sp_executesql @read_ref_sql,
				N'@ref_value_out binary(16) output, @a_id binary(16)', 
				@a_id = @a_curr_row_id,
				@ref_value_out = @a_ref_value output;
			     
				if @a_ref_value is not null
				begin		     
					set @a_ref_value_was_found = 0;
			        
					--Enumerating all referenced tables
					declare @ref_table_id binary(16),
					@ref_table_cursor cursor;
					set @ref_table_cursor = cursor fast_forward read_only for
					select ref_table_id from meta_ref_targets
					where column_id = @ref_column_id;
			        
					open @ref_table_cursor;
					fetch next from @ref_table_cursor into @ref_table_id;
			         
					while @@fetch_status = 0
					begin                
						set @a_ref_table_name = dbo.p_get_table_name(@ref_table_id);
			     
						--Checking reference value existence
						declare @check_ref_sql nvarchar(500);
						set @check_ref_sql = 'select @a_row_count_out = 1 where exists(select 1 from tbl_'
						+ @a_ref_table_name
						+ ' where id = @a_ref_value)';
			            
						exec sp_executesql @check_ref_sql,
						N'@a_row_count_out int output, @a_ref_value binary(16)', 
						@a_ref_value = @a_ref_value,
						@a_row_count_out = @a_row_count output;
			     
						--If there are reference values
						if @a_row_count > 0
						begin
							--Restoring other reference values too (putting them in the list)
							declare @restore_other_rows_sql nvarchar(500);							
							set @restore_other_rows_sql = 'insert into ##rows_to_be_restored(row_id, table_id, table_name)'
							+ ' select @row_id, @table_id, @table_name'
							+ ' where @row_id not in (select row_id from ##rows_to_be_restored)';

							exec sp_executesql @restore_other_rows_sql,
							N'@row_id binary(16), @table_id binary(16), @table_name varchar(26)', 
							@row_id = @a_ref_value,
							@table_id = @ref_table_id,
							@table_name = @a_ref_table_name;
			            
							set @a_ref_value_was_found = 1;
						end;
			            
						fetch next from @ref_table_cursor into @ref_table_id;
					 end;
			         
					 close @ref_table_cursor;
					 deallocate @ref_table_cursor;
			         
					 --If reference value was not found
					 if @a_ref_value_was_found = 0
					 begin
						--Row id str
						declare @a_row_id_str varchar(32);
						--Ref value str
						declare @a_ref_value_str varchar(32);

						set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);
						set @a_ref_value_str = dbo.p_bin_to_str(@a_ref_value);

						raiserror ('Restoring when there is no referenced row (id = %s, ref_value = %s) (#error_code = 20002)',
						16, 1, @a_row_id_str, @a_ref_value_str);
						rollback;
						return;
					 end;                
				 end;
			     
				 fetch next from @ref_cursor into @ref_column_id, @ref_column_name;
			 end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Restoring the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'update tbl_' + @a_curr_table_name
			+ ' set is_active = 1, deleted_by = null, deleted_on = null where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the restored rows list
			insert @restored_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table_row';

go

--Function of deleting a table row
create procedure meta_delete_table_row(
	--Table id
	@a_table_id binary(16),
	--Row id
	@a_row_id binary(16))
with execute as owner
as
begin
	declare
	--Current row id
	@a_curr_row_id binary(16),
	--Current table id
	@a_curr_table_id binary(16),
    --Current table name
    @a_curr_table_name varchar(26);
    
    set nocount on;
    
    set xact_abort on;
    begin tran;
    
    --A table that stores rows to be deleted
    if object_id('tempdb..##rows_to_be_deleted') is null --Creating a #temp table is slow
		create table ##rows_to_be_deleted(
			--Row id
			row_id binary(16) primary key,
			--Table id
			table_id binary(16) not null);
		
	--A table that stores deleted rows
	declare @deleted_rows table (
		--Row id
		row_id binary(16) primary key);
	
	--Saying that we want to delete the initial row
	set @a_curr_table_name = dbo.p_get_table_name(@a_table_id);		
	insert ##rows_to_be_deleted(row_id, table_id)
	values(@a_row_id, @a_table_id);

    while (exists(select 1 from ##rows_to_be_deleted))
    begin
   
		--Getting a row to be deleted
		select top 1 
			@a_curr_table_id = table_id,
			@a_curr_row_id = row_id
		from ##rows_to_be_deleted;
		
		set @a_curr_table_name = dbo.p_get_table_name(@a_curr_table_id);
		
		delete ##rows_to_be_deleted
		where row_id = @a_curr_row_id;
		
		--If the current row is not deleted already	
		if (not exists(select 1 from @deleted_rows where row_id = @a_curr_row_id))
		begin
		
			declare
			--Ref table id
			@a_ref_table_id binary(16),
			--Ref table name
			@a_ref_table_name varchar(26),
			--Ref column name
			@a_ref_column_name varchar(26),
			--Row count
			@a_row_count int;
			 
			--Enumerating all references on the current table
			declare @ref_cursor cursor;
			set @ref_cursor = cursor fast_forward read_only for
			select column_id, cascade_delete from meta_ref_targets
			where ref_table_id = @a_curr_table_id;
			 
			declare @a_ref_column_id binary(16),
			@a_ref_cascade_delete numeric(1);

			open @ref_cursor;
			fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;

			while @@fetch_status = 0
			begin  
			
				--Reading ref column properties
				select @a_ref_column_name = column_name,
				@a_ref_table_id = table_id
				from dbo.meta_ref_columns
				where column_id = @a_ref_column_id;

				set @a_ref_table_name = dbo.p_get_table_name(@a_ref_table_id);
	    
				--Deleting other reference values too (putting them in the list of row to be deleted)
				declare @delete_other_rows_sql nvarchar(500);
				set @delete_other_rows_sql = 'insert into ##rows_to_be_deleted(row_id, table_id)'
				+ ' select id, @table_id from tbl_' + @a_ref_table_name
				+ ' where ' + @a_ref_column_name + ' = @row_id'
				+ ' and id not in (select row_id from ##rows_to_be_deleted)';

				exec sp_executesql @delete_other_rows_sql,
				N'@row_id binary(16), @table_id binary(16)', 
				@row_id = @a_curr_row_id,
				@table_id = @a_ref_table_id;
				
				fetch next from @ref_cursor into @a_ref_column_id, @a_ref_cascade_delete;
			end;
			 
			close @ref_cursor;
			deallocate @ref_cursor;
		
			--Deleting the current row
			declare @soft_delete_row_sql nvarchar(500);
			set @soft_delete_row_sql = 'delete from tbl_' + @a_curr_table_name
			+ ' where id=@row_id';
			
			exec sp_executesql @soft_delete_row_sql,
			N'@row_id binary(16)', 
			@row_id = @a_curr_row_id;
			
			--Adding the current row to the deleted rows list
			insert @deleted_rows values(@a_curr_row_id);
		end;
    end;
    
    --Not dropping #temp tables
	
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_table_triggers';

go

--Function of creating table triggers
create procedure p_create_table_triggers(
    --Table id
    @a_table_id binary(16))
as
begin
	declare
	--Table name
    @a_table_name varchar(26);
    --Table id str
	declare @a_table_id_str varchar(32);
	set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);    

    --Trigger for inserting a row
    execute ('create trigger i'
    + @a_table_id_str
    + ' on tbl_' + @a_table_name
    + ' instead of insert'
    + ' as begin'
    + ' set nocount on;'
    + ' select * into #inserted from inserted;'
    + ' update #inserted set id = convert(binary(16), newid()) where id is null;'
    + ' update #inserted set owner = lower(original_login()) where owner is null;'
    + ' update #inserted set owner = lower(owner),'
    + ' created_on = getdate(), updated_on = getdate(),'
    + ' created_by = lower(original_login()), updated_by = lower(original_login()),'
    + ' row_version = convert(binary(16), newid());'
    + ' insert into tbl_' + @a_table_name + ' select * from #inserted;'
    + ' drop table #inserted;'
    + ' end;');
end;

go

exec dbo.p_drop_proc_if_exists 'p_drop_table_triggers';

go

--Function of dropping table triggers
create procedure p_drop_table_triggers(
	--Table id
	@a_table_id binary(16))
as
begin
	declare
	--Table id str
	@a_table_id_str varchar(32);
	
	set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
	
	exec ('drop trigger i' + @a_table_id_str);
end;

go

exec dbo.p_drop_proc_if_exists 'p_create_table_view';

go

--Function of creating table view
create procedure p_create_table_view(
    --Table id
    @a_table_id binary(16))
as
begin
	declare
	--Recycle bin option
    @a_recycle_bin_option numeric(10),
	--Table name
    @table_name varchar(26),
    --Unit level security
    @a_unit_level_security numeric(1),
    --Comma delimited list of column names
    @column_name_list varchar(max);
    --Table id str
	declare @a_table_id_str varchar(32);
	
	set @column_name_list = '';
	set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);  
	  
    set @table_name = dbo.p_get_table_name(@a_table_id);
    
    --Reading table properties
    select @a_unit_level_security = unit_level_security
    from meta_tables
    where table_id = @a_table_id;
    
    --Reading options
    select @a_recycle_bin_option = int_value
    from meta_options
    where option_name = 'RECYCLE_BIN';
    
    --Reading table columns
    declare @col_column_name varchar(26),
    @col_cursor cursor;
    set @col_cursor = cursor fast_forward read_only for
    select column_name from meta_table_columns
    where table_id = @a_table_id
    and column_name <> 'is_active'
    and column_name <> 'deleted_by'
    and column_name <> 'deleted_on'
    order by order_index;
    
    open @col_cursor;
    fetch next from @col_cursor into @col_column_name;
    
    while @@fetch_status = 0
    begin   
		if len(@column_name_list) > 0
			set @column_name_list = @column_name_list + ',';
            
        set @column_name_list = @column_name_list + @col_column_name;
		
		fetch next from @col_cursor into @col_column_name;
    end;
    
    close @col_cursor;
    deallocate @col_cursor; 

    --Creating the view
    declare @a_create_view_sql varchar(max);
    
    set @a_create_view_sql = 'create view '
    + @table_name
    + ' as select '
    + @column_name_list + ' from tbl_'
    + @table_name + ' t'
    + ' where 1 = 1';
    
    --If recycle bin is enabled
    if @a_recycle_bin_option = 1
        set @a_create_view_sql = @a_create_view_sql + ' and is_active = 1';
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		set @a_create_view_sql = @a_create_view_sql
		--The full access
		+ ' and (exists(select 1 from meta_table_acl_full_access where table_id=0x'
		+ @a_table_id_str + ' and user_name=lower(original_login()) and access_type=1)'
		--The unit level access
		+ ' or t.owner in (select u.user_name from meta_table_acl_unit_access s'
		+ ' inner join meta_user_units u on s.unit_id=u.unit_id'
		+ ' where table_id=0x' + @a_table_id_str 
		+ ' and s.user_name=lower(original_login()) and access_type=1)'
		--The own rows access
		+ ' or t.owner=lower(original_login()) and exists(select 1 from meta_table_acl_own_rows'
		+ ' where table_id=0x'
        + @a_table_id_str + ' and user_name=lower(original_login()) and access_type=1))';
    exec (@a_create_view_sql);
    
    --Creating delete trigger
    declare @a_delete_trigger_sql varchar(max);
    
    set @a_delete_trigger_sql = 'create trigger d'
    + @a_table_id_str
    + ' on ' + @table_name
    + ' with execute as owner'
    + ' instead of delete as begin'
    + ' set nocount on;'
    + ' declare @a_row_id binary(16), @a_owner varchar(26),'
    + ' @a_has_full_rights numeric,'
    + ' @a_value_cursor cursor;'
      --Checking for full access rights
    + ' select @a_has_full_rights = count(*) from meta_table_acl_full_access where table_id=0x'
	+     @a_table_id_str + ' and user_name=lower(original_login()) and access_type=4;'
    + ' set @a_value_cursor = cursor fast_forward read_only for select id, owner from deleted;'
	+ ' open @a_value_cursor;'
	+ ' fetch next from @a_value_cursor into @a_row_id, @a_owner;'
	+ ' while @@fetch_status = 0 begin'
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		--Checking the delete permission
		set @a_delete_trigger_sql = @a_delete_trigger_sql		
		      --The unit-level access
		+ '   if @a_has_full_rights = 0 and not exists(select 1 from meta_table_acl_unit_access s'
		+ '     inner join meta_user_units u on s.unit_id=u.unit_id'
		+ '     where table_id=0x' + @a_table_id_str 
		+ '       and s.user_name=lower(original_login()) and access_type=4 and u.user_name=@a_owner)'
		      --The own rows access
	    + '   and not exists(select 1 from meta_table_acl_own_rows where table_id=0x'
		+     @a_table_id_str + ' and @a_owner=lower(original_login())'
		+     ' and user_name=lower(original_login()) and access_type=4)'   
		+ '   begin'
		+ '     declare @a_row_id_str varchar(32);'
		+ '     set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);'
		+ '     raiserror(''Insufficient privileges to delete the row (id = %s) (#error_code = 20004)'', 16, 1, @a_row_id_str);'
		+ '     return;'
		+ '   end;';
		
	--If recycle bin is enabled
    if @a_recycle_bin_option = 1
        --Performing the soft delete
        set @a_delete_trigger_sql = @a_delete_trigger_sql 
        + ' exec dbo.meta_soft_delete_table_row 0x' + @a_table_id_str + ', @a_row_id;';
    else
        --Performing the delete
        set @a_delete_trigger_sql = @a_delete_trigger_sql 
        + ' exec dbo.meta_delete_table_row 0x' + @a_table_id_str + ', @a_row_id;';
		
    set @a_delete_trigger_sql = @a_delete_trigger_sql
    + '   fetch next from @a_value_cursor into @a_row_id, @a_owner;'
    + ' end;'
    + ' close @a_value_cursor;'
    + ' deallocate @a_value_cursor;'
	----------------------------------------
    + ' end;';
    
    exec (@a_delete_trigger_sql);
    
    
    --Trigger for update
    declare @a_update_trigger_sql varchar(max);
    
    set @a_update_trigger_sql = 'create trigger j'
    + @a_table_id_str
    + ' on ' + @table_name
    + ' with execute as owner'
    + ' instead of update as begin'
    + ' set nocount on;'
    
    --If unit level security is ON
    if @a_unit_level_security = 1
		set @a_update_trigger_sql = @a_update_trigger_sql
		-- Checking update permission
		+ ' declare @a_row_id binary(16),'
		+ ' @a_old_owner varchar(26),'
		+ ' @a_has_full_rights numeric,'
		+ ' @a_new_value_cursor cursor, @a_row_count numeric;'
		    --Checking the full access rights
		+ ' select @a_has_full_rights = count(*) from meta_table_acl_full_access where table_id=0x'
		+     @a_table_id_str + ' and user_name=lower(original_login()) and access_type=3;'
		+ ' set @a_new_value_cursor = cursor fast_forward read_only for select id from inserted;'
		+ ' open @a_new_value_cursor;'
		+ ' fetch next from @a_new_value_cursor into @a_row_id;'
		+ ' while @@fetch_status = 0'
		+ ' begin'
		+ '   select @a_old_owner = owner from deleted where id = @a_row_id;'
		      --The unit-level access
		+ '   if @a_has_full_rights = 0 and not exists(select 1 from meta_table_acl_unit_access s'
		+ '     inner join meta_user_units u on s.unit_id=u.unit_id'
		+ '     where table_id=0x' + @a_table_id_str 
		+ '       and s.user_name=lower(original_login()) and access_type=3 and u.user_name=@a_old_owner)'
		      --The own rows access
	    + '   and not exists(select 1 from meta_table_acl_own_rows where table_id=0x'
		+     @a_table_id_str + ' and @a_old_owner=lower(original_login())'
		+     ' and user_name=lower(original_login()) and access_type=3)' 
		+ '   begin'
		+ '     declare @a_row_id_str varchar(32);'
		+ '     set @a_row_id_str = dbo.p_bin_to_str(@a_row_id);'
		+ '     raiserror(''Insufficient privileges to update the row (id = %s) (#error_code = 20003)'', 16, 1, @a_row_id_str);'
		+ '     return;'
		+ '   end;'
		+ '   fetch next from @a_new_value_cursor into @a_row_id;'
		+ ' end;'
		+ ' close @a_new_value_cursor;'
		+ ' deallocate @a_new_value_cursor;';
	
	set @a_update_trigger_sql = @a_update_trigger_sql
    --Performing the update----------------------------------------
    + ' update tbl_' + @table_name
    + ' set tbl_' + @table_name + '.owner = lower(inserted.owner),'
    + ' tbl_' + @table_name + '.updated_on = getdate(),'
    + ' tbl_' + @table_name + '.updated_by = lower(original_login()),'
    + ' tbl_' + @table_name + '.row_version = convert(binary(16), newid())';

	--Reading all custom columns
	declare @a_column_name varchar(26),
	@a_column_cursor cursor;
    
	set @a_column_cursor = cursor fast_forward read_only for
	select column_name from meta_table_columns
	where table_id = @a_table_id
	and customization_level > 0;
    
	open @a_column_cursor;
	fetch next from @a_column_cursor into @a_column_name;
    
	while @@fetch_status = 0
	begin
		set @a_update_trigger_sql = @a_update_trigger_sql +
		', tbl_' + @table_name + '.' + @a_column_name + '=inserted.' + @a_column_name;
		fetch next from @a_column_cursor into @a_column_name;
	end;
    
	close @a_column_cursor;
	deallocate @a_column_cursor;
    
    set @a_update_trigger_sql = @a_update_trigger_sql 
	+ ' from tbl_' + @table_name
	+ ' inner join inserted on tbl_' + @table_name +'.id = inserted.id;'
	+ ' end;';
    
    exec (@a_update_trigger_sql);
    
    --Setting table view grants
    exec dbo.p_set_table_view_grants @a_table_id;
end;

go

exec dbo.p_drop_proc_if_exists 'p_set_table_view_grants';

go

--Function of setting table view grants
create procedure p_set_table_view_grants(
    --Table id
    @a_table_id binary(16))
as
begin
	declare
	--Table name
    @a_table_name varchar(26);
    
    set @a_table_name = dbo.p_get_table_name(@a_table_id);

	--Reading table acl
	declare @access_type numeric(1),
	@principal_name varchar(26),
	@acl_cursor cursor;
	set @acl_cursor = cursor fast_forward read_only for
	select access_type, principal_name from meta_table_acl
    where table_id = @a_table_id;
    
    open @acl_cursor;
    fetch next from @acl_cursor into @access_type, @principal_name; 
	
    while @@fetch_status = 0
    begin        
        if @access_type = 1
            exec ('grant select on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 2
			exec ('grant insert on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 3
			exec ('grant update on ' + @a_table_name + ' to ' + @principal_name);
        else if @access_type = 4
			exec ('grant delete on ' + @a_table_name + ' to ' + @principal_name);
        
        fetch next from @acl_cursor into @access_type, @principal_name; 
    end;
    
    close @acl_cursor;
    deallocate @acl_cursor;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_table_not_exists';

go

--Function of checking that table doesn't exist
create procedure p_check_table_not_exists(
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(26),
    --Result
    @a_result numeric(1) output)
as
begin
	declare
    --Table id str
    @a_table_id_str varchar(32);
    
    --Searching for the table id
    if exists(select 1 from meta_tables
		where table_id = @a_table_id)
	begin
		set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);
        raiserror('A table with the specified id was already added (table_id = %s) (#error_code = 20025)',
        16, 1, @a_table_id_str);
        set @a_result = 0;
        return;
    end;
    
    --Searching for the table name
    if exists(select 1 from meta_tables
		where table_name = @a_table_name)
    begin
        raiserror('A table with the specified name was already added (table_name = %s) (#error_code = 20026)',
        16, 1, @a_table_name);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'p_check_table_exists';

go

--Function of checking table existence
create procedure p_check_table_exists(
    --Table id
    @a_table_id binary(16),
    --Result
    @a_result numeric(1) output)
as  
begin
    --Searching for the table id
    if not exists(select 1from meta_tables
		where table_id = @a_table_id)
    begin
		declare
		--Table id str
		@a_table_id_str varchar(32);
		set @a_table_id_str = dbo.p_bin_to_str(@a_table_id);

        raiserror('Specified table was not found (table_id = %s) (#error_code = 20027)',
        16, 1, @a_table_id_str);
        set @a_result = 0;
        return;
    end;
    
    set @a_result = 1;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_add_table';

go

--Function of creating new user table
create procedure meta_add_table(
    --Table id
    @a_table_id binary(16),
    --Table name
    @a_table_name varchar(max),
    --Id column id
    @a_id_column_id binary(16),
    --Owner column id
    @a_owner_column_id binary(16),
    --CreatedBy column id
    @a_created_by_column_id binary(16),
    --CreatedOn column id
    @a_created_on_column_id binary(16),
    --UpdatedBy column id
    @a_updated_by_column_id binary(16),
    --UpdatedOn column id
    @a_updated_on_column_id binary(16),
    --DeletedBy column id
    @a_deleted_by_column_id binary(16),
    --DeletedOn column id
    @a_deleted_on_column_id binary(16),
    --IsActive column_id
    @a_is_active_column_id binary(16),
    --RowVersion column id
    @a_row_version_column_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare @a_result numeric(1);

    exec dbo.p_check_table_not_exists @a_table_id, @a_table_name, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;    
    begin tran;

    --Inserting metadata
    insert into meta_tables(table_id, table_name)
    values (@a_table_id, @a_table_name);

    --Creating user table
    exec ('create table tbl_'
    + @a_table_name
    + '(id binary(16), '
    + 'owner varchar(26) not null, '
    + 'is_active numeric(1) default 1 not null, '
    + 'created_by varchar(26) not null, '
    + 'created_on datetime not null, '
    + 'updated_by varchar(26) not null, '
    + 'updated_on datetime not null, '
    + 'row_version binary(16) not null,'
    + 'deleted_by varchar(26) null, '
    + 'deleted_on datetime null, '
    + 'check(len(rtrim(ltrim(owner)))>0),'
    + 'check(is_active=0 or is_active=1))');
    
    --Inserting system columns
    insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_id_column_id, @a_table_id, 'id', 1, N'Id', 0, 0, N'The row id.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_owner_column_id, @a_table_id, 'owner', 1, N'Owner', 1, 0, 26, N'The row owner.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_created_by_column_id, @a_table_id, 'created_by', 1, N'Created by', 2, 0, 26, N'The name of the user that created the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_created_on_column_id, @a_table_id, 'created_on', 1, N'Created on', 3, 0, N'The date and time of the row creation.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_updated_by_column_id, @a_table_id, 'updated_by', 1, N'Updated by', 4, 0, 26, N'The name of the user that updated the row last.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_updated_on_column_id, @a_table_id, 'updated_on', 1, N'Updated on', 5, 0, N'The date and time of the last row update.');
    
    insert into meta_string_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, max_length, description)
    values(@a_deleted_by_column_id, @a_table_id, 'deleted_by', 0, N'Deleted by', 6, 0, 26, N'The name of the user that deleted the row.');
    
    insert into meta_datetime_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_deleted_on_column_id, @a_table_id, 'deleted_on', 0, N'Deleted on', 7, 0, N'The date and time of the row deletion.');
    
    insert into meta_bool_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_is_active_column_id, @a_table_id, 'is_active', 1, N'Is active', 8, 0, N'True if the row was not soft deleted.');
    
    insert into meta_guid_columns(column_id, table_id,
    column_name, is_not_null, display_name, order_index, customization_level, description)
    values(@a_row_version_column_id, @a_table_id, 'row_version', 1, N'Row version', 9, 0, N'The version of the row.');
        
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;

    --Creating table triggers
    exec dbo.p_create_table_triggers @a_table_id;
    
    --Granting access to schema administrators
    declare @acl_user_name varchar(26),
    @acl_cursor cursor;
    set @acl_cursor = cursor fast_forward read_only for
    select user_name from meta_users
    where can_change_schema = 1;
    
    open @acl_cursor;
    fetch next from @acl_cursor into @acl_user_name;
    
    while @@fetch_status = 0
	begin
        exec ('grant select, insert, update, delete on tbl_' 
        + @a_table_name + ' to ' + @acl_user_name);
        
        fetch next from @acl_cursor into @acl_user_name;
    end;
    
    close @acl_cursor;
    deallocate @acl_cursor;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_delete_table';

go

--Function of deleting user table
create procedure meta_delete_table(
    --Table id
    @a_table_id binary(16))
with execute as owner
as
begin
	set nocount on;
	declare
	--Table name
    @a_table_name varchar(30),
    --Result
    @a_result numeric(1);
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    set xact_abort on;
    begin tran;

    set @a_table_name = dbo.p_get_table_name(@a_table_id);

    --Deleting the metadata
    delete from meta_tables
    where table_id = @a_table_id;

	--Dropping table view
	exec dbo.p_drop_view_if_exists @a_table_name;

	--Dropping the table
	set @a_table_name = 'tbl_' + @a_table_name;
	exec dbo.p_drop_table_if_exists @a_table_name;
   
	commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_rename_table';

go

--Function of table renaming
create procedure meta_rename_table(
    --Table id
    @a_table_id binary(16),
    --New table name
    @a_new_table_name varchar(max))
with execute as owner
as
begin
	declare
	--Old table name
    @a_old_table_name varchar(30),
    --Result
    @a_result numeric(1);
    
    set nocount on;
    
    exec dbo.p_check_table_exists @a_table_id, @a_result output;
    
    if @a_result = 0 return;
    
    --Searching for a table with the same name
    if exists(select 1 from meta_tables
		where table_name = @a_new_table_name)
    begin
        raiserror('A table with the specified name was already added (table_name = %s) (#error_code = 20026)',
        16, 1, @a_new_table_name);
        return;
    end;
    
    set @a_old_table_name = dbo.p_get_table_name(@a_table_id);

    --If there is no need for renaming
    if @a_old_table_name = @a_new_table_name
        return;
    
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_tables
    set table_name = @a_new_table_name
    where table_id = @a_table_id;    
    
    --Dropping table view
    exec dbo.p_drop_view_if_exists @a_old_table_name;
    
    --Dropping table triggers
    exec dbo.p_drop_table_triggers @a_table_id;
    
    --Renaming the table
    set @a_old_table_name = 'tbl_' + @a_old_table_name;
    declare @a_new_table_name_modified varchar(30);
    set @a_new_table_name_modified = 'tbl_' + @a_new_table_name;
    exec sp_rename @a_old_table_name, @a_new_table_name_modified, 'OBJECT';
    
    --Creating table triggers
    exec dbo.p_create_table_triggers @a_table_id;
    
    --Recreating index triggers for all table columns
    declare @a_column_id binary(16),
    @col_cursor cursor;
    set @col_cursor = cursor fast_forward read_only for
    select column_id from meta_table_columns
    where table_id = @a_table_id;
    
    open @col_cursor;
    fetch next from @col_cursor into @a_column_id;
    
    while @@fetch_status = 0
    begin   
		--Recreating column index triggers
        exec dbo.p_recreate_col_index_triggers @a_column_id;
        
		fetch next from @col_cursor into @a_column_id;
    end;
    
    close @col_cursor;
    deallocate @col_cursor; 

    
    --Creating table view
    exec dbo.p_create_table_view @a_table_id;
    
    commit;
end;

go

exec dbo.p_drop_proc_if_exists 'meta_set_recycle_bin';

go

--Function of setting the availability of the recycle bin
create procedure meta_set_recycle_bin(
    --The availability of the recycle bin
    @a_recycle_bin_enabled numeric(10))
with execute as owner
as
begin
	declare
	--Table name
    @a_table_name varchar(26),
    --Table id
    @a_table_id binary(16);
    
    set nocount on;
    set xact_abort on;
    begin tran;
    
    --Updating the metadata
    update meta_options
    set int_value = @a_recycle_bin_enabled
    where option_name = 'RECYCLE_BIN';
    
	declare @a_table_cursor cursor;
	set @a_table_cursor = cursor fast_forward read_only for
	select table_id, table_name from meta_tables;
	
	open @a_table_cursor;
	fetch next from @a_table_cursor into @a_table_id, @a_table_name;

	--Recreating table views
	while @@fetch_status = 0
	begin
		exec dbo.p_drop_view_if_exists @a_table_name;
        exec dbo.p_create_table_view @a_table_id;
        
		fetch next from @a_table_cursor into @a_table_id, @a_table_name;
	end;
	
	close @a_table_cursor;
	deallocate @a_table_cursor;
	
	commit;    
end;

go